
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib prefix="sec" uri="http://www.springframework.org/security/tags" %>
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
		<title>JSP Page</title>
	</head>
	<body>
		<h3>DB Test</h3>
		<form action="dbtest.jsp" method="post">
			<textarea name="sqls" rows="10" cols="100"><%=(request.getParameter("sqls") == null)? "":request.getParameter("sqls") %></textarea>
			<br/>
			<input type="submit"/>
		</form>
		<%
			String sqls = request.getParameter("sqls");
			if (sqls != null && sqls.length() > 0) {
				String[] sqlArr = sqls.split(";");
				for (String sql : sqlArr) {
					if (sql.trim().length() > 0) {
						query(sql, out);
					}
				}
			}
		%>

<textarea name="testSqls" rows="10" cols="100">
* ACL Tables
select * from ACL_SID;
select * from ACL_CLASS;
select * from ACL_OBJECT_IDENTITY;
select * from ACL_ENTRY;

* Res Tables
select * from USER;
select * from ROLE;
select * from RESC;
select * from USER_ROLE;
select * from RESC_ROLE;
</textarea>
	</body>
</html>

<%!
void query(String sql, JspWriter out) throws Exception {
	Connection conn = getConnection();
	PreparedStatement ps = conn.prepareStatement(sql);
	ResultSet rs = ps.executeQuery();
	ResultSetMetaData rsmd = rs.getMetaData();
	int columnCount = rsmd.getColumnCount();
	
	out.println("<h5>" + sql + "</h5>");
	out.println("<table border=\"1\">");
	out.println("<tr>");
	for (int i = 0; i < columnCount; i++) {
		out.println("<th>" + rsmd.getColumnName(i + 1) + "</th>");
	}
	out.println("</tr>");

	while(rs.next()) {
		out.println("<tr>");
		for (int i = 0; i < columnCount; i++) {
			out.println("<td>" + rs.getObject(i + 1) + "</td>");
		}
		out.println("</tr>");
	}
	out.println("</table>");
	
	rs.close();
	conn.close();
}
Connection getConnection() throws Exception {
	String driverClassName = "org.hsqldb.jdbcDriver";
	//String url = "jdbc:hsqldb:mem:.";
	//String url = "jdbc:hsqldb:res:hsqldb/acl";
	String url = "jdbc:hsqldb:res:hsqldb/resmap";
	String username = "sa";
	String password = "";
	Class.forName(driverClassName);
	Connection conn = DriverManager.getConnection(url, username, password);
	return conn;
}
%>
